package com.emep.emeptime.repository;

import com.emep.emeptime.entity.DataAqiInfo;
import org.springframework.data.jpa.domain.Specification;
import org.springframework.data.jpa.repository.JpaRepository;
import org.springframework.data.jpa.repository.Query;
import org.springframework.data.repository.query.Param;

import java.util.List;

/**
 * @author wangcc
 * @Description: aqi数据持久层
 * @date 2018/3/278:55
 * @version V1.0
 */
public interface DataAqiInfoRepository extends JpaRepository<DataAqiInfo,String> {


    List<DataAqiInfo> findDataAqiInfoByTimePoint(String TimePoint);


    List<DataAqiInfo> findAll();


    @Query(value = "SELECT  SUM(co) AS 'co' FROM data_aqi_info WHERE  time_point BETWEEN :startdate AND  :enddate  AND area_id =:aId ;",nativeQuery = true)
    double getCoSum(@Param("startdate")String startdate, @Param("enddate")String enddate, @Param("aId")String aId);

    @Query(value = "SELECT  SUM(no2) AS 'no2' FROM data_aqi_info WHERE time_point BETWEEN :startdate AND :enddate AND area_id = :areaid ;" ,nativeQuery = true)
    double getNo2Sum(@Param("startdate")String startdate, @Param("enddate")String enddate, @Param("areaid")String areaid);

   @Query(value = "SELECT  SUM(o3) AS 'o3' FROM data_aqi_info WHERE time_point BETWEEN :startdate AND  :enddate AND area_id = :areaId ;",nativeQuery = true)
    double getO3Sum(@Param("startdate")String startdate, @Param("enddate")String enddate, @Param("areaId")String areaId);

    @Query(value = "SELECT  SUM(pm10) AS 'pm10' FROM data_aqi_info WHERE time_point BETWEEN :startdate AND  :enddate AND area_id = :areaId ;",nativeQuery = true)
    double getPm10Sum(@Param("startdate")String startdate, @Param("enddate")String enddate, @Param("areaId")String areaId);

    @Query(value = "SELECT  SUM(pm25) AS 'pm25' FROM data_aqi_info WHERE time_point BETWEEN :startdate AND  :enddate AND area_id = :areaId ;",nativeQuery = true)
    double getPm25Sum(@Param("startdate")String startdate, @Param("enddate")String enddate, @Param("areaId")String areaId);

    @Query(value = "SELECT  SUM(so2) AS 'so2' FROM data_aqi_info WHERE time_point BETWEEN :startdate AND  :enddate AND area_id = :areaId ;",nativeQuery = true)
    double getSo2Sum(@Param("startdate")String startdate, @Param("enddate")String enddate, @Param("areaId")String areaId);

}
